package com.srit.yoggsaron.test;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import org.junit.After;
import org.junit.Before;
import org.junit.Test;
import org.junit.runner.RunWith;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.test.context.TestPropertySource;
import org.springframework.test.context.junit4.SpringRunner;

@RunWith(SpringRunner.class)
@TestPropertySource("classpath:application.properties")
public class CreateTableColumns {

	private Connection con = null;
	private Statement sta = null;

	@Value("${spring.datasource.url}")
	private String url;
	@Value("${spring.datasource.username}")
	private String username;
	@Value("${spring.datasource.password}")
	private String password;
	@Value("${spring.datasource.driver-class-name}")
	private String driverName;
	
	
	

	@Before
	public void connection() {
		try {
			Class.forName(driverName);
			con = DriverManager.getConnection(url, username, password);
			if (con != null)
				sta = con.createStatement();

		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}

	private List<String> loadAllTableName() {
		String dataBaseName = url.substring(url.lastIndexOf("/") + 1);
		if (dataBaseName.indexOf("?") > 0)
			dataBaseName = dataBaseName.substring(0, dataBaseName.indexOf("?"));

//		String sql1 = "select table_name from information_schema.tables where table_schema='" + dataBaseName + "'";
//		String sql2 = "select table_name from information_schema.views where table_schema='" + dataBaseName + "'";

		ResultSet rs = null;
		List<String> names = new ArrayList<String>();

		try {
			rs = sta.executeQuery("show tables");
			while (rs.next()) {
				
				String name = rs.getString(1);
				if(name!=null && (!name.toLowerCase().equals("databasechangeloglock")) &&((!name.toLowerCase().equals("databasechangelog"))) )
					names.add(rs.getString(1));
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return names;
	}

	@Test
	public void readTable() {

		List<String> tableNames = loadAllTableName();
//		tableNames.clear();
//		tableNames.add("record_task_view"); 
		for( int i =0;i< tableNames.size();i++ )
		{
			System.out.println( );
			System.out.println( );
			System.out.println("*****  Table Name :  "+tableNames.get(i)+"   ***********************************************************" );
			
			List<TableInfo> dicViewlist = readTable(tableNames.get(i));
			printMessage( tableNames.get(i) ,  dicViewlist );
			
			System.out.println( );
			System.out.println( );
			System.out.println( );
			System.out.println( );
			System.out.println("****************************************************************" );
			
		}

	}

	private void printMessage(String tableName, List<TableInfo> list) {
		if (list == null || list.size() == 0) {
			return;
		}

		List<String> columnNameList = new ArrayList<String>();
		List<String> javaNameList = new ArrayList<String>();
		for (int i = 0; i < list.size(); i++) {
			TableInfo info = list.get(i);

			String columnName = info.getColumnName();// row.getCell(0).getStringCellValue();
			String common = info.getComment();
			String type = info.getDataType();// row.getCell(2).getStringCellValue();

			columnNameList.add(columnName.toLowerCase());

			String jName = getJavaName(columnName);
			javaNameList.add(jName);

			System.out.print("private   " + getJavaType(type) + "  " + jName + "= null ;");
			System.out.println("/**    " + common + "     */");

		}

		printNameList(columnNameList, "", "");
		printNameList(javaNameList, " #{", "} ");

		printNameMapping(javaNameList, columnNameList);

		printUpdate(javaNameList, columnNameList);

	}

	private List<TableInfo> readTable(String tableName) {
		if (con == null || sta == null)
			return null;
		List<TableInfo> list = new ArrayList<TableInfo>();
		ResultSet rs = null;
		String sql = "select   column_name  , data_type ,  character_maximum_length    ,  column_comment "
				+ " from information_schema.columns where table_name  ='" + tableName + "'  ";
		try {
			rs = sta.executeQuery(sql);

			while (rs.next()) {
				TableInfo info = new TableInfo();

				info.setColumnName(rs.getString("column_name"));
				info.setComment(rs.getString("column_comment"));
				info.setDataType(rs.getString("data_type"));
				info.setLength(rs.getString("character_maximum_length"));

				list.add(info);

			}

		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				rs.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}

		return list;
	}

	@After
	public void destory() {
		try {
			con.close();

		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}
	
	
	private void println()
	{
		System.out.println();
		System.out.println();
		System.out.println();
		System.out.println();
		System.out.println();
		System.out.println();
	}

	private void printUpdate(List<String> names, List<String> columns) {

		
		println();
		
		int row = names.size() <= columns.size() ? names.size() : columns.size();

//		for (int i = 0; i < row; i++) {
//			System.out.print ("<if test=\"" + names.get(i) + " != null  &amp;&amp; " + names.get(i) + " !='' \" >");
//			System.out.println("	" + columns.get(i) + " = #{" + names.get(i) + "}, ");
//			System.out.println("</if>");
//		}

		
		
		
		for (int i = 0; i < row; i++) { 
			
			String s =  "<if test=\"" + names.get(i) + " != null   " ;
			
			if(names.get(i).toLowerCase().indexOf("time")<=0 )
				s+="  &amp;&amp; " + names.get(i) + " !='' \" ";
			else
				s+="  \" " ;
			s+="  >" ;
									
			System.out.println( s );	
			System.out.println("	" + columns.get(i) + " = #{" + names.get(i) + "}, ");
			System.out.println("</if>");
		}
		
		
	}

	private void printNameMapping(List<String> names, List<String> columns) {
		println();

		int row = names.size() <= columns.size() ? names.size() : columns.size();

		for (int i = 0; i < row; i++) {
			System.out.println(
					" <result property=\"" + names.get(i) + "\"" + '\t' + "column=\"" + columns.get(i) + "\" /> ");
		}

	}

	private String getJavaName(String columnName) {
		if (columnName == null || columnName.trim().length() == 0)
			return "";

		String[] cell = columnName.split("_");
		StringBuffer sb = new StringBuffer();

		for (int i = 0; i < cell.length; i++) {
			String c = cell[i];

			if (i == 0) {
				sb.append(c.toLowerCase());
			} else
				sb.append(c.substring(0, 1).toUpperCase() + c.substring(1, c.length()).toLowerCase());
		}
		return sb.toString();
	}

	private void printNameList(List<String> names, String prefix, String suffix) {
		println();
		for (int i = 0; i < names.size(); i++) {
			if (i > 0 && i % 5 == 0)
				System.out.println();
			System.out.print(prefix + names.get(i) + suffix);
			if (i != names.size() - 1)
				System.out.print(" , ");

		}
	}

	private String getJavaType(String type) {
		if (type == null)
			return "";
		type = type.toLowerCase();

		if (type.toLowerCase().startsWith("varchar"))
			return "String";
		else if (type.toLowerCase().startsWith("datetime"))
			return "Date";
		else if (type.toLowerCase().startsWith("float"))
			return "Float";
		else if (type.toLowerCase().startsWith("int"))
			return "Integer";
		else if (type.toLowerCase().startsWith("text"))
			return "String";
		else if (type.toLowerCase().startsWith("bigint"))
			return "Integer";
		else
			return "String";
	}

}

class TableInfo {
	private String columnName = null;
	private String dataType = null;
	private String length = null;
	private String comment = null;

	public String getColumnName() {
		return columnName;
	}

	public void setColumnName(String columnName) {
		this.columnName = columnName;
	}

	public String getDataType() {
		return dataType;
	}

	public void setDataType(String dataType) {
		this.dataType = dataType;
	}

	public String getLength() {
		return length;
	}

	public void setLength(String length) {
		this.length = length;
	}

	public String getComment() {
		return comment;
	}

	public void setComment(String comment) {
		this.comment = comment;
	}
}
